<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">

<%
String query = request.getParameter("queryString");
if (query==null) {
	query = "";
}
Connection conn = null;
String queryResultView = "";
try {	
	conn = getConnection();
	queryResultView = buildView(conn, query);
} catch (Exception e) {
	out.println("Error : " + e.getMessage());
	e.printStackTrace();
} finally {
	if (conn!=null && !conn.isClosed()) {
		conn.close();
	}			    			
}
%>


<%@page import="java.sql.Connection"%>
<%@page import="java.sql.Driver"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.util.List"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.ResultSetMetaData"%>
<%@page import="java.util.ArrayList"%>
<%@page import="java.sql.SQLException"%>


<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Insert title here</title>
</head>
<body>

<div id="maincontent"> 
<span style="font-size:20;">Run SQL Queries</span>&nbsp;&nbsp;


<form id="reportForm" name="reportForm" method="POST" action="qe.jsp"> 
  <table width="100%" border="0">
    <tr>
      <td>
		<TEXTAREA name="queryString" id="queryString" cols="100" rows="8"><%=query%></TEXTAREA>
      </td>
    </tr>

    <tr>
      <td> 
        
		<table width="300" border="0">
          <tr> 
            <td width="109"><input type="submit" name="Submit" value="Execute Query"></td>
          	<td width="60"><input type="button" name="Reset" value="Reset" onClick="return doReset();"></td>            
          </tr>  
        </table>

      </td>
    </tr>
  </table>

  <input type="hidden" id="queryStringHidden" />

</form>  

<hr style="height:1; width:840px"; align="left">  


<%=queryResultView%>

</div>

</body>

<script language="JavaScript">

function doReset() {	
	$("queryString").innerHTML = "";
	$("reportForm").submit();
}

function $(elementId) {
	return document.getElementById(elementId);
}

</script>

</html>


<%!
Connection getConnection() throws Exception {

    Connection conn = null;
	String driverName = "org.postgresql.Driver";
	String databaseUrl = "jdbc:postgresql://domain/database";
    String user = "user";
	String pwd = "password";
    Driver driver = (Driver) Class.forName(driverName).newInstance();
    DriverManager.registerDriver(driver);
    conn = DriverManager.getConnection(databaseUrl, user, pwd);
    return conn;
}
%>


<%!
String buildView(Connection conn, String query) {	
	if (query==null || query.trim().length()==0) {
		return "";
	}
	
	if (!query.toUpperCase().startsWith("SELECT")) {
		String msg = "<font color='red' size='+1'>Only SELECT statement is allowed to be executed.";
		return msg;
	}			
	StringBuffer sb = new StringBuffer();	
	try {
		sb.append("<table class='myList'>");
		sb.append("<thead>"); 
		sb.append("<tr>");
		String[] columnNames =  getColumnNames(conn, query);
		for (int i=0; i<columnNames.length; i++) {
			sb.append("<th>" + columnNames[i] + "</th>");
		}
		sb.append("</tr>");
		sb.append("</thead>");
		
		List objList = getResultSet(conn, columnNames, query);

		sb.append("<tbody>");
		
		int rowCount = 0;
		for (int i=0; i<objList.size(); i++) {
			Object row = objList.get(i);
			if (rowCount%2==0) {
				sb.append("<tr class='even'>");
			} else {
				sb.append("<tr class='odd'>");
			}
			Class clazz = row.getClass();
			String name = clazz.getCanonicalName();

			//if (name.equalsIgnoreCase("java.lang.Object[]")) {
			if (name.equalsIgnoreCase("java.util.ArrayList")) {
				//Object[] columns = (Object[]) row;
				ArrayList columns = (ArrayList) row;
				for (int j=0; j<columns.size(); j++) {					
					sb.append("<td>" + columns.get(j) + "</td>");
				}
					
			} else {
				sb.append("<td>" + row + "</td>");
			}
			sb.append("</tr>");
			rowCount++;
		}
		sb.append("</tbody>");
		
		sb.append("</table>");
	} catch (Exception e) {
		sb.append("<font color='red' size='+1'>Error : " + e.getClass() + " : " + e.getMessage() + "</font>");
		if (e instanceof SQLException) {
			SQLException sqlException = (SQLException) e;
			int errorCode = sqlException.getErrorCode();
			sb.append("<font color='red' size='+1'>Error : " + errorCode + "</font>");
		}		
	}
	return sb.toString();
}
%>


<%!
String[] getColumnNames(Connection conn, String query) throws Exception {
	String[] columnNameArray = null;
	Statement stmt = null;
	ResultSet rs = null;
	int columnCount = 0;

	try {
		stmt = conn.createStatement();
		rs = stmt.executeQuery(query);
		ResultSetMetaData metadata = rs.getMetaData();
		columnCount = metadata.getColumnCount();
		columnNameArray = new String[columnCount];
		for (int i=1; i<=columnCount; i++) {
			String columnName = metadata.getColumnName(i);
			if (columnCount<=1 && columnName.trim().length()==0) {
				columnName = computedColumn(query);
			}
			columnNameArray[i-1] = columnName;
		}
	} catch (Exception e) {
		throw e;
	} finally {
		try {
			if (rs!=null) {
				rs.close();
			}
			if (stmt!=null) {
				stmt.close();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}	    
	return columnNameArray;
}
%>

<%!
String computedColumn(String query) {
	
	String computedColumn = "";
	int index1 = query.indexOf("select") + "select".length();
	int index2 = query.indexOf("from");
	if (index1>=0 && index2>=0) {
		computedColumn =  query.substring(index1, index2).trim();
	}
	return computedColumn;
}
%>

<%!
List getResultSet(Connection conn, String[] columnNames, String query) throws Exception {
	List list = new ArrayList();
	Statement stmt = null;
	ResultSet rs = null;	
	try {
		stmt = conn.createStatement();
		rs = stmt.executeQuery(query);
		while (rs.next()) {
			List columnList = new ArrayList();
			for (int i=0; i<columnNames.length; i++) {
				Object obj = rs.getObject(i+1);
				columnList.add(obj);
			}
			list.add(columnList);
		}
	} catch (Exception e) {
		throw e;
	} finally {
		try {
			if (rs!=null) {
				rs.close();
			}
			if (stmt!=null) {
				stmt.close();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	return list;
}
%>
